# pip install pyodbc

import pyodbc
import os
import re
import sys

DATABASE=sys.argv[1]

if DATABASE == 'GBOS':
    file_path = 'GBOS-BOSSQL-GBOS'
elif DATABASE == 'IFT':
    file_path = 'GBOS-BOSSQL-IFT'
elif DATABASE == 'CARE':
    file_path = 'GBOS-BOSCRMSQL-CARE'
elif DATABASE == 'CAS':
    file_path = 'GBOS-BOSCRMSQL-CAS'
elif DATABASE == 'VIP':
    file_path = 'GBOS-BOSCRMSQL-VIP'
elif DATABASE == 'IDEA':
    file_path = 'GBOS-BOSTOKENSQL-IDEA'
else:
    file_path = ''
    print('The parameter is not valid')
    exit(1)


print('get started...')
# Define the connection string to the Azure SQL database
conn_str = (
    "Driver={ODBC Driver 17 for SQL Server};"
    "Server=dv-bos-eus-gbos-001-sql.database.windows.net;"
    f"Database={DATABASE};"
    "UID=vincent.tang@greendotcorp.com;"
    "PWD=xxx;"
    "Authentication=ActiveDirectoryInteractive;"
)
'''
To connect to your Azure SQL database using Python with Multi-Factor Authentication (MFA), you can use the ActiveDirectoryInteractive authentication method. This method will prompt you to authenticate interactively, similar to how you do it in SQL Server Management Studio (SSMS)
'''
# Connect to the Azure SQL database
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Retrieve the deployment sequence from the control table
cursor.execute("SELECT SequenceID, TableName FROM dbo.TableDeploymentSequenceControl where IsDeployedToAzureSQL=0 AND ErrorDuringDeployment IS NULL ORDER BY SequenceID asc")
deployment_sequence = cursor.fetchall()

print('cursor.fetchall..')

# Define the folder containing the SQL script files
folder_path = rf"C:\Users\vincent.tang.NEXTESTATE\OneDrive - GREEN DOT CORPORATION\Desktop\0924 TimeZoneConversion-part1\{file_path}\Tables"

# Deploy each table creation script based on the sequence
for row in deployment_sequence:
    table_name = row.TableName
    script_file_path = os.path.join(folder_path, f"{table_name}.sql")

    # Check if the script file exists
    if os.path.exists(script_file_path):
        # Read the content of the script file
        print('script_file_path: ',script_file_path)
        with open(script_file_path, 'r', encoding="utf8") as file:
            script_content = file.read()
            commands = re.split(r'(?i)^\s*GO\s*$', script_content, flags=re.MULTILINE)

        # Execute each command separately
        for command in commands:
            if command.strip():  # Skip empty commands
                cursor.execute(command)
                cursor.execute('update dbo.TableDeploymentSequenceControl set IsDeployedToAzureSQL=1 where TableName=?', table_name)
                conn.commit()
        # Execute the script content
        #cursor.execute(script_content)
        #conn.commit()
        print(f"Successfully deployed {table_name}.sql")
    else:
        print(f"Script file {table_name}.sql not found in {folder_path}")
        errMsg=f"Script file {table_name}.sql not found in {folder_path}"
        cursor.execute('update dbo.TableDeploymentSequenceControl set ErrorDuringDeployment=? where TableName=?',(errMsg, table_name))
        conn.commit()
# Close the database connection
cursor.close()
conn.close()

'''
Steps to Follow:
Update the Connection String: Replace <your_server_name>, <your_database_name>, <your_username>, and <your_password> with your actual Azure SQL database credentials.
Place SQL Files: Ensure your SQL script files are named exactly as the TableName values in your control table and are located in c:\folder1.
Run the Script: Execute the script to automatically deploy the SQL files in the order specified by SequenceID.
This script will connect to your Azure SQL database, retrieve the deployment sequence, and execute each SQL script file in the specified order. Let me know if you need any further assistance!
'''
